package src;
import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.*;

/**
 * Created by IntelliJ IDEA.
 * User: nelson
 * Date: Jan 4, 2009
 * Time: 4:03:44 PM
 * To change this template use File | Settings | File Templates.
 */
public class Data {
    private Connection con;
    private String userName;
    private String password;
    private String url = "jdbc:mysql://localhost:3306/baseDados";
    private Statement st;
    private ResultSet rs;
    private int res;


    /** Inicia o driver, abre a ligação e cria um statement */
    public Data(){
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Problemas na inicializaçao do driver \n");
        }
        this.userName= "baseDados";
        this.password= "baseDados";
        this.url = "jdbc:mysql://localhost:3306/baseDados";
        try {
            this.con = DriverManager.getConnection(url, userName, password);
            st = con.createStatement();
        } catch (SQLException e) {
            System.out.println("Problemas no acesso a Base de Dados\n");
        }
    }


    public void criaTabelas() throws SQLException {

        String[] query = new String[5];

        query[0] = "Create table Encarregado(Encarregado_id Int PRIMARY KEY, Nome VARCHAR(100))";
        query[1] = "Create table Professor(Cod_prof Int PRIMARY KEY,Cod_disc Int REFERENCES Disciplina(Cod_disc), Nome VARCHAR(100))";
        query[2] = "Create table Disciplina(Cod_disc Int PRIMARY KEY, nome VARCHAR(100))";
        query[3] = "Create table Aluno(Cod_aluno Int PRIMARY KEY, nome VARCHAR(100), idade SMALLINT, enc_id Int REFERENCES Encarregado(Encarregado_id))";
        query[4] = "Create table Notas (Cod_Aluno Int REFERENCES Aluno(Cod_aluno),cod_disciplina Int REFERENCES Disciplina(Cod_disc), nota SMALLINT, PRIMARY KEY(Cod_Aluno,cod_disciplina))";

        for(int i =0; i<5;i++){
            try{
                int res = st.executeUpdate(query[i]);
            }catch(MySQLSyntaxErrorException e){
                System.out.println("Erro na criaçao da tabela"+(i+1));
            }
        }

    }

    public void eliminaTabelas() throws SQLException {

        String[] query = new String[5];

        query[0] = "Drop table Encarregado";
        query[1] = "Drop table Professor";
        query[2] = "Drop table Disciplina";
        query[3] = "Drop table Aluno";
        query[4] = "Drop table Notas ";

        for(int i =0; i<5;i++){
            try{
                res = st.executeUpdate(query[i]);
            }catch(MySQLSyntaxErrorException e){
                System.out.println("Erro na criaçao da tabela"+(i+1));
            }
        }
    }

    public int insereTabela(String query) throws SQLException {
        try{
                res = st.executeUpdate(query);
            }catch(MySQLSyntaxErrorException e){
                System.out.println("Erro na Insercao");
            }
        return res;
        }

    public void executaQuery(String query) throws SQLException{
        try{
            ResultSet rs = st.executeQuery(query);
        }catch(MySQLSyntaxErrorException e){
            System.out.println("Erro na Execuçao");
        }
    }

    public int getNextIdEncarregado() throws SQLException{

        ResultSet rs;
        int num = 0;

         try {
            rs = st.executeQuery("SELECT MAX(Encarregado_id) FROM Encarregado");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

             //
        }
        return(num+1);
    }

    public int getNextIdProf() throws SQLException{

        ResultSet rs;
        int num = 0;

         try {
            rs = st.executeQuery("SELECT MAX(Cod_prof) FROM Professor");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

             //
        }
        return(num+1);
    }
    public int getNextIdAluno() throws SQLException{

        ResultSet rs;
        int num = 0;

        try {
            rs = st.executeQuery("SELECT MAX(Cod_aluno) FROM Aluno");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

            //
        }
        return(num+1);
    }

    public int getMaxIDDisc() throws SQLException{

        ResultSet rs;
        int num = 0;

        try {
            rs = st.executeQuery("SELECT MAX(Cod_disc) FROM Disciplina");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

            //
        }
        return(num);
    }

}


